package com.eascs.admin.o2o.compensatory.dao;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.o2o.compensatory.entity.CompensatoryDataEnter;
import com.eascs.admin.o2o.compensatory.entity.DCUpdateData;
import com.eascs.admin.o2o.compensatory.entity.DCUpdateNumVo;
import com.eascs.admin.o2o.compensatory.entity.O2oDCLoanVo;
import com.eascs.admin.o2o.loan.entity.O2oEaLoanDataEnity;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;

@Repository
public class O2oDCLoanDao extends BaseO2oDao {

	// 代偿信息分页查询
	public PageResult<Object[]> getDCByPage(O2oDCLoanVo vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT * FROM ( SELECT oeld.id, oeld.bill_code AS bill_code, ");
		querySql.append(" opr.province_name AS province_name, oeld.loaner_name AS loaner_name, ");
		querySql.append(" CONCAT( ol.ltd_code, '-', ol.ltd_name ) as ltd_name, oeld.pay_type AS paytype, ");
		querySql.append(" DATE_FORMAT(oeld.pay_date, '%Y-%m-%d') AS pay_date, ");
		querySql.append(" oeld.status AS isplaceonfile,opr.province_code as province ");
		querySql.append(" FROM o2o_ea_loan_data oeld LEFT JOIN o2o_loanapply ol ");
		querySql.append(" ON oeld.bill_code = ol.bill_code LEFT JOIN o2o_platform_regional opr on oeld.ltd = opr.platform_id WHERE oeld.is_del = 'N' ) t WHERE 1 = 1");
		if (!StringUtil.isEmpty(vo.getBillCode())) {
			querySql.append(" AND t.bill_code like ?");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if (!StringUtil.isEmpty(vo.getLoanerName())) {
			querySql.append(" AND t.loaner_name like ?");
			sqlPageQuery.addLikeParam(vo.getLoanerName());
		}
		if (!StringUtil.isEmpty(vo.getPayType())) {
			querySql.append(" AND t.isplaceonfile = ? ");
			sqlPageQuery.addParam(vo.getPayType());
		}
		if (!StringUtil.isEmpty(vo.getProvince())) {
			querySql.append(" AND t.province = ? ");
			sqlPageQuery.addParam(vo.getProvince());
		}
		if (!StringUtil.isEmpty(vo.getStartdate())) {
			querySql.append(" AND t.pay_date >= ? ");
			sqlPageQuery.addParam(vo.getStartdate());
		}
		if (!StringUtil.isEmpty(vo.getEnddate())) {
			querySql.append(" AND t.pay_date < date_add(?, INTERVAL 1 DAY) ");
			sqlPageQuery.addParam(vo.getEnddate());
		}
		querySql.append(" ORDER BY t.pay_date DESC ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}

	// 查询代偿资料录入表
	public CompensatoryDataEnter findloanDataIdById(String id) {
		String sql = "select * from compensatory_data_enter where loan_data_id = ?";
		return this.findEntity(sql, CompensatoryDataEnter.class, id);
	}
	
	//保存代偿录入资料
	public int saveEnterInfo(CompensatoryDataEnter entity){
		return this.insert(entity);
	}
	
	//根据id查询billcode
	public String findBillCodeById(String loanDataId){
		String sql = "select bill_code from o2o_ea_loan_data where id = ?";
		return this.findString(sql, loanDataId);
	}
	
	//修改代偿录入资料
	public int updateEnterInfo(CompensatoryDataEnter vo) {
		String sql = "UPDATE compensatory_data_enter SET transfer_invitation_num = ?, receipt_num = ?,"
				+ " ens_bill_num = ?, refund_detail_num = ? WHERE id = ?";
		return this.execute(sql, vo.getTransferInvitationNum(), vo.getReceiptNum(), vo.getEnsBillNum(),
				vo.getRefundDetailNum(), vo.getId());
	}
	
	//根据id修改剩余量
	public int addResidueNum(DCUpdateNumVo vo) {
		String sql = " UPDATE compensatory_data_enter SET transfer_invitation_residue_num = transfer_invitation_residue_num + ?, receipt_residue_num = receipt_residue_num + ?, ens_bill_residue_num = ens_bill_residue_num + ?, refund_detail_residue_num = refund_detail_residue_num + ? WHERE id = ? ";
		return this.execute(sql, vo.getTinvitationNum(), vo.getrNum(), vo.geteBillNum(), vo.getrDetailNum(), vo.getId());
	}
	
	//根据id查询省份
	public String findProvinceById(String loanDataId) {
		String sql = "SELECT o.province_name FROM o2o_ea_loan_data o WHERE o.id = ?";
		return this.findString(sql, loanDataId);
	}
	
	//根据省份查询省份简称
	public String findJCByProvince(String province) {
		String sql = "SELECT p.item_code FROM pub_dditem_o2o p WHERE p.dd_no = '8' AND p.item_name = ?";
		return this.findString(sql, province);
	}
	
	//根据billcode查询代偿数据
	public CompensatoryDataEnter findByBillCode(String billcode){
		String sql = " select * from compensatory_data_enter where bill_code = ? ";
		return this.findEntity(sql, CompensatoryDataEnter.class, billcode);
	}
	
	//修改借出和剩余数量
	public int updateLandRDate(DCUpdateData vo){
		String sql = " UPDATE compensatory_data_enter SET transfer_invitation_residue_num = ?, transfer_invitation_loan_num = ?, receipt_residue_num = ?, receipt_loan_num = ? , ens_bill_residue_num = ?, ens_bill_loan_num = ?, refund_detail_residue_num = ?, refund_detail_loan_num = ? WHERE bill_code = ? ";
		return this.execute(sql, vo.getTransferInvitationResidueNum(), vo.getTransferInvitationLoanNum(), vo.getReceiptResidueNum(), vo.getReceiptLoanNum(), vo.getEnsBillResidueNum(), vo.getEnsBillLoanNum(), vo.getRefundDetailResidueNum(), vo.getRefundDetailLoanNum(),vo.getBillCode());
	}
	
	public O2oEaLoanDataEnity findEaLoanData(String billCode){
		String sql = "select * from o2o_ea_loan_data o where o.bill_code = ?";
		return this.findEntity(sql, O2oEaLoanDataEnity.class, billCode);
	}
	
	/*//借出修改
	public int updateLend(DCUpdateNumVo vo) {
		String sql = " UPDATE compensatory_data_enter SET transfer_invitation_residue_num = transfer_invitation_residue_num - ?, transfer_invitation_loan_num = transfer_invitation_loan_num + ?, receipt_residue_num = receipt_residue_num - ?, receipt_loan_num = receipt_loan_num + ? , ens_bill_residue_num = ens_bill_residue_num - ?, ens_bill_loan_num = ens_bill_loan_num + ?, refund_detail_residue_num = refund_detail_residue_num - ?, refund_detail_loan_num = refund_detail_loan_num + ? WHERE bill_code = ? ";
		return this.execute(sql, vo.getTinvitationNum(), vo.getTinvitationNum(), vo.getrNum(), vo.getrNum(), vo.geteBillNum(), vo.geteBillNum(), vo.getrDetailNum(), vo.getrDetailNum(), vo.getBillCode());
	}
	
	//归还修改
	public int updateGiveBack(DCUpdateNumVo vo) {
		String sql = " UPDATE compensatory_data_enter SET transfer_invitation_residue_num = transfer_invitation_residue_num + ?, transfer_invitation_loan_num = transfer_invitation_loan_num - ?, receipt_residue_num = receipt_residue_num + ?, receipt_loan_num = receipt_loan_num - ? , ens_bill_residue_num = ens_bill_residue_num + ?, ens_bill_loan_num = ens_bill_loan_num - ?, refund_detail_residue_num = refund_detail_residue_num + ?, refund_detail_loan_num = refund_detail_loan_num - ? WHERE bill_code = ? ";
		return this.execute(sql, vo.getTinvitationNum(), vo.getTinvitationNum(), vo.getrNum(), vo.getrNum(), vo.geteBillNum(), vo.geteBillNum(), vo.getrDetailNum(), vo.getrDetailNum(), vo.getBillCode());
	}*/
}
